<?php
class TgdongdoanphicbsController extends AppController {

	var $name = 'Tgdongdoanphicbs';
	var $uses=array('Tgdongdoanphicb','Canbo','Tgiachidoancb','Thang','Chidoansv','Doanphicb','Namhoc');
	var $helpers=array('ctable');
	function index() {
		$this->Tgdongdoanphicb->recursive = 0;
		$this->set('tgdongdoanphicbs', $this->paginate());
	}

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid tgdongdoanphicb', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('tgdongdoanphicb', $this->Tgdongdoanphicb->read(null, $id));
	}

	function add() {
		if (!empty($this->data)) {
			$this->Tgdongdoanphicb->create();
			if ($this->Tgdongdoanphicb->save($this->data)) {
				$this->Session->setFlash(__('The tgdongdoanphicb has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The tgdongdoanphicb could not be saved. Please, try again.', true));
			}
		}
		$namhocs = $this->Tgdongdoanphicb->Namhoc->find('list');
		$canbos = $this->Tgdongdoanphicb->Canbo->find('list');
		$thangs = $this->Tgdongdoanphicb->Thang->find('list');
		$chidoansvs = $this->Tgdongdoanphicb->Chidoansv->find('list');
		$this->set(compact('namhocs', 'canbos', 'thangs', 'chidoansvs'));
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid tgdongdoanphicb', true));
			$this->redirect(array('action' => 'index'));
		}
		if (!empty($this->data)) {
			if ($this->Tgdongdoanphicb->save($this->data)) {
				$this->Session->setFlash(__('The tgdongdoanphicb has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The tgdongdoanphicb could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Tgdongdoanphicb->read(null, $id);
		}
		$namhocs = $this->Tgdongdoanphicb->Namhoc->find('list');
		$canbos = $this->Tgdongdoanphicb->Canbo->find('list');
		$thangs = $this->Tgdongdoanphicb->Thang->find('list');
		$chidoansvs = $this->Tgdongdoanphicb->Chidoansv->find('list');
		$this->set(compact('namhocs', 'canbos', 'thangs', 'chidoansvs'));
	}

	function delete($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for tgdongdoanphicb', true));
			$this->redirect(array('action'=>'index'));
		}
		if ($this->Tgdongdoanphicb->delete($id)) {
			$this->Session->setFlash(__('Tgdongdoanphicb deleted', true));
			$this->redirect(array('action'=>'index'));
		}
		$this->Session->setFlash(__('Tgdongdoanphicb was not deleted', true));
		$this->redirect(array('action' => 'index'));
	}

	function doanp1($chidoan=null,$namhoc=null)
	{
	if ($chidoan==null || $namhoc==null)
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
		else
		{
		// can luu: sinhvien_id, thang_id, ngaydong, ghichu
        if(!empty($this->data['Tgdongdoanphicb']['dadong'])){
            //phai xoa de data cu de luu data moi
                $this->Tgiachidoancb->query("delete from tgdongdoanphicbs 
                            where namhoc_id='".$namhoc."' and chidoansv_id='".$chidoan."'");
                            
                foreach($this->data['Tgdongdoanphicb']['dadong'] as $value){
                    $record['Tgdongdoanphicb']['thang_id']=substr($value,0,strpos($value,'_'));
                    $record['Tgdongdoanphicb']['sinhvien_id']=substr($value,strpos($value,'_')+1);
                    $record['Tgdongdoanphicb']['ngaydong']=date('Y-m-d');
                    $record['Tgdongdoanphicb']['namhoc_id']=$namhoc;
                    $record['Tgdongdoanphicb']['chidoansv_id']=$chidoan;
                    if(!$this->Tgdongdoanphicb->saveAll($record)){
                   	    $this->Session->setFlash(__('Việc lưu không hợp lệ', true),'error');
                        break;
                    }
                }
                $this->Session->setFlash(__('Bạn đã cập nhật đoàn phí thành công!!', true),'success');  
        }
        
        //lay thong tin chi doan hien dang cap nhat doan phi
        $chidoansv=$this->Chidoansv->find('first',array(
                'conditions'=>array('Chidoansv.id'=>$chidoan),
                'fields'=>array('Chidoansv.id','tenchidoan','Khoa.tenkhoa','khoahoc_id'),
                'recursive'=>0));
        /** ***********************************
         * Lay data cot dong
         *************************************** */
        //tim cac sinhvien thuoc chidoan (lay id cua sv)
        $cb=$this->Tgiachidoancb->find('list',array(
                'conditions'=>array('chidoansv_id'=>$chidoan,'ngaychuyen'=>null),
                'fields'=>array('canbo_id','canbo_id'),'recursive'=>-1));
        
		//hien thi sinhvien can cu vao $sv 
        $canbos=$this->Canbo->find('list',array('conditions'=>array('Canbo.id'=>$cb),
                'fields'=>array('id','name'),'recursive'=>-1));
		//lay cac thang cua namhoc
        $thangs=$this->Thang->find('list',array('fields'=>array('id','thang'),
                'conditions'=>array('namhoc_id'=>$namhoc)));
        
        /** ************************************
         * cac sinh vien da dong doan phi
         *************************************** */
        if(!empty($thangs))
        	$thang_id = array_keys($thangs);
        else
        	$thang_id= array();
        $doanphi = $this->Doanphicb->find('list',array(
        					'fields'=>array('thang_id','sotien'),
        					'conditions'=>array('thang_id'=>$thang_id)));
        //cot "name" dc dinh  nghia trong Model cua Tgdongdoanphicb, lay du lieu de kiem tra xem sinhvien do
        //da dong vao thang nao
        $dulieu=$this->Tgdongdoanphicb->find('list',array(
                'conditions'=>array('namhoc_id'=>$namhoc,'chidoansv_id'=>$chidoan),
                'fields'=>array('id','name'),
                'recursive'=>-1));
        $dadongs = $this->Tgdongdoanphicb->find('all',array(
        					'fields'=>array('Tgdongdoanphicb.thang_id','count(Tgdongdoanphicb.canbo_id) as dadong'),
        					'conditions'=>array('Tgdongdoanphicb.namhoc_id'=>$namhoc,
        										'Tgdongdoanphicb.chidoansv_id'=>$chidoan),
        					'group'=>array('thang_id')));
        $dathu = array();
        foreach ($dadongs as $dadong)
        {
        	$dathu[$dadong['Tgdongdoanphicb']['thang_id']] =$dadong[0]['dadong'] * $doanphi[$dadong['Tgdongdoanphicb']['thang_id']]; 
        }
        $nhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc),
        									'fields'=>array('id','namhoc')));
        
        $this->set(compact('canbos','thangs','chidoansv','namhoc','dulieu','nhoc','dathu','chidoan','doanphi'));
        
		}
	}
	
	function doanp($chidoan=null)
	{
		if ($chidoan==null)
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
		$namhoc =$this->Session->read('namhocht_id');
		// can luu: sinhvien_id, thang_id, ngaydong, ghichu
        if(!empty($this->data['Tgdongdoanphicb']['dadong']))
        {            
        	$error = 0;                
                foreach($this->data['Tgdongdoanphicb']['dadong'] as $value){
                	$arrsv =split('_', $value);
                    $record['Tgdongdoanphicb']['thang_id']=$arrsv[0];
                    $record['Tgdongdoanphicb']['canbo_id']=$arrsv[1];
                    $record['Tgdongdoanphicb']['ngaydong']=date('Y-m-d');
                    $record['Tgdongdoanphicb']['namhoc_id']=$this->data['Tgdongdoanphicb']['namhoc_id'];
                    $record['Tgdongdoanphicb']['chidoansv_id']=$chidoan;
                    $record['Tgdongdoanphicb']['user_id']=$this->Session->read('userid');
                    if(!$this->Tgdongdoanphicb->saveAll($record)){
                    	$error = 1;
                   	    $this->Session->setFlash(__('Việc lưu không hợp lệ', true),'error');
                        break;
                    }
                }
                 if($error!=1)
                	$this->Session->setFlash(__('Bạn đã cập nhật đoàn phí thành công!!', true),'success');    
        }
        //lay thong tin chi doan hien dang cap nhat doan phi
        $chidoansv=$this->Chidoansv->find('first',array(
                'conditions'=>array('Chidoansv.id'=>$chidoan),
                'fields'=>array('Chidoansv.id','tenchidoan','Khoa.tenkhoa','khoahoc_id'),
                'recursive'=>0));
        /** ***********************************
         * Lay data cot dong
         *************************************** */
        //tim cac sinhvien thuoc chidoan (lay id cua sv)
        $cb=$this->Tgiachidoancb->find('list',array(
                'conditions'=>array('chidoansv_id'=>$chidoan,'ngaychuyen'=>null),
                'fields'=>array('canbo_id','canbo_id'),'recursive'=>-1));
                
		//hien thi sinhvien can cu vao $sv 
        $canbos=$this->Canbo->find('list',array('conditions'=>array('Canbo.id'=>$cb),
                                                //'OR'=>array(array('ngayvaodoan !='=>NULL),
                                                //array('ngayvaodoan !='=>'0000-00-00')),'ngayttdoan '=>NULL),
                						'fields'=>array('id','name'),'recursive'=>-1));
        //lay cac thang cua namhoc
        $thangs=$this->Thang->find('list',array('fields'=>array('id','thang'),
                'conditions'=>array('namhoc_id'=>$namhoc)));
        
        /** ************************************
         * cac sinh vien da dong doan phi
         *************************************** */
        if(!empty($thangs))
        	$thang_id = array_keys($thangs);
        else
        	$thang_id= array();
        $doanphi = $this->Doanphicb->find('list',array(
        					'fields'=>array('thang_id','sotien'),
        					'conditions'=>array('thang_id'=>$thang_id)));
        //cot "name" dc dinh  nghia trong Model cua Tgdongdoanphicb, lay du lieu de kiem tra xem sinhvien do
        //da dong vao thang nao
        $dulieu=$this->Tgdongdoanphicb->find('list',array(
                'conditions'=>array('namhoc_id'=>$namhoc,'chidoansv_id'=>$chidoan),
                'fields'=>array('name','ngaydong'),
                'recursive'=>-1));
        
	$urs=$this->Tgdongdoanphicb->find('all',array(
                'conditions'=>array('Tgdongdoanphicb.namhoc_id'=>$namhoc,'Tgdongdoanphicb.chidoansv_id'=>$chidoan),
                'fields'=>array('Tgdongdoanphicb.name',"CONCAT(Z1user.maso, '_', Z1user.hoten) as `Z1user__name`"),
                'recursive'=>1));
        $users = array();
        foreach ($urs as $ur)
        {
        	$users[$ur['Tgdongdoanphicb']['name']] =$ur['Z1user']['name'];
        }
        $dadongs = $this->Tgdongdoanphicb->find('all',array(
        					'fields'=>array('Tgdongdoanphicb.thang_id','count(Tgdongdoanphicb.canbo_id) as dadong'),
        					'conditions'=>array('Tgdongdoanphicb.namhoc_id'=>$namhoc,
        										'Tgdongdoanphicb.chidoansv_id'=>$chidoan,
        									 'OR'=>array(array('Canbo.ngayttdoan'=>null),
        												array('Canbo.ngayttdoan'=>'0000-00-00'))),
        					'group'=>array('thang_id')));
        
        $dathu = array();
        foreach ($dadongs as $dadong)
        {
        	$dathu[$dadong['Tgdongdoanphicb']['thang_id']] =$dadong[0]['dadong'] * $doanphi[$dadong['Tgdongdoanphicb']['thang_id']]; 
        }
        $nhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc),
        									'fields'=>array('id','namhoc')));
        $namhocs = $this->Namhoc->find('list',array('fields'=>array('id','namhoc')));

        $this->set(compact('namhocs','canbos','thangs','chidoansv','namhoc','dulieu','nhoc','dathu','chidoan','doanphi','users'));
        
	}

	function updatedoanp($chidoan)
	{
		$this->layout='ajax';
		$namhoc = $this->data['Tgdongdoanphicb']['namhoc_id'];
		//lay thong tin chi doan hien dang cap nhat doan phi
        $chidoansv=$this->Chidoansv->find('first',array(
                'conditions'=>array('Chidoansv.id'=>$chidoan),
                'fields'=>array('Chidoansv.id','tenchidoan','Khoa.tenkhoa','khoahoc_id'),
                'recursive'=>0));
        /** ***********************************
         * Lay data cot dong
         *************************************** */
        //tim cac sinhvien thuoc chidoan (lay id cua sv)
        $cb=$this->Tgiachidoancb->find('list',array(
                'conditions'=>array('chidoansv_id'=>$chidoan,'ngaychuyen'=>null),
                'fields'=>array('canbo_id','canbo_id'),'recursive'=>-1));
                
		//hien thi sinhvien can cu vao $sv 
        $canbos=$this->Canbo->find('list',array('conditions'=>array('Canbo.id'=>$cb),
                                                //'OR'=>array(array('ngayvaodoan !='=>NULL),
                                                //array('ngayvaodoan !='=>'0000-00-00')),'ngayttdoan '=>NULL),
                						'fields'=>array('id','name'),'recursive'=>-1));
        //lay cac thang cua namhoc
        $thangs=$this->Thang->find('list',array('fields'=>array('id','thang'),
                'conditions'=>array('namhoc_id'=>$namhoc)));
        
        /** ************************************
         * cac sinh vien da dong doan phi
         *************************************** */
        if(!empty($thangs))
        	$thang_id = array_keys($thangs);
        else
        	$thang_id= array();
        $doanphi = $this->Doanphicb->find('list',array(
        					'fields'=>array('thang_id','sotien'),
        					'conditions'=>array('thang_id'=>$thang_id)));
        //cot "name" dc dinh  nghia trong Model cua Tgdongdoanphicb, lay du lieu de kiem tra xem sinhvien do
        //da dong vao thang nao
        $dulieu=$this->Tgdongdoanphicb->find('list',array(
                'conditions'=>array('namhoc_id'=>$namhoc,'chidoansv_id'=>$chidoan),
                'fields'=>array('name','ngaydong'),
                'recursive'=>-1));
	$urs=$this->Tgdongdoanphicb->find('all',array(
                'conditions'=>array('Tgdongdoanphicb.namhoc_id'=>$namhoc,'Tgdongdoanphicb.chidoansv_id'=>$chidoan),
                'fields'=>array('Tgdongdoanphicb.name',"CONCAT(Z1user.maso, '_', Z1user.hoten) as `Z1user__name`"),
                'recursive'=>1));
        $users = array();
        foreach ($urs as $ur)
        {
        	$users[$ur['Tgdongdoanphicb']['name']] =$ur['Z1user']['name'];
        }
        $dadongs = $this->Tgdongdoanphicb->find('all',array(
        					'fields'=>array('Tgdongdoanphicb.thang_id','count(Tgdongdoanphicb.canbo_id) as dadong'),
        					'conditions'=>array('Tgdongdoanphicb.namhoc_id'=>$namhoc,
        										'Tgdongdoanphicb.chidoansv_id'=>$chidoan,
        									 'OR'=>array(array('Canbo.ngayttdoan'=>null),
        												array('Canbo.ngayttdoan'=>'0000-00-00'))),
        					'group'=>array('thang_id')));
        
        $dathu = array();
        foreach ($dadongs as $dadong)
        {
        	$dathu[$dadong['Tgdongdoanphicb']['thang_id']] =$dadong[0]['dadong'] * $doanphi[$dadong['Tgdongdoanphicb']['thang_id']]; 
        }
        $nhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc),
        									'fields'=>array('id','namhoc')));
        $namhocs = $this->Namhoc->find('list',array('fields'=>array('id','namhoc')));

        $this->set(compact('namhocs','canbos','thangs','chidoansv','namhoc','dulieu','nhoc','dathu','chidoan','doanphi','users'));
	}
	
	function xuatexcel($chidoan=null,$namhoc=null)
	{
		if ($chidoan==null || $namhoc==null)
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
		$this->layout='';
		// can luu: sinhvien_id, thang_id, ngaydong, ghichu
        if(!empty($this->data['Tgdongdoanphicb']['dadong']))
        {            
        	$error = 0;                
                foreach($this->data['Tgdongdoanphicb']['dadong'] as $value){
                	$arrsv =split('_', $value);
                    $record['Tgdongdoanphicb']['thang_id']=$arrsv[0];
                    $record['Tgdongdoanphicb']['canbo_id']=$arrsv[1];
                    $record['Tgdongdoanphicb']['ngaydong']=date('Y-m-d');
                    $record['Tgdongdoanphicb']['namhoc_id']=$namhoc;
                    $record['Tgdongdoanphicb']['chidoansv_id']=$chidoan;
                    if(!$this->Tgdongdoanphicb->saveAll($record)){
                    	$error = 1;
                   	    $this->Session->setFlash(__('Việc lưu không hơp lệ', true),'error');
                        break;
                    }
                }
                 if($error!=1)
                	$this->Session->setFlash(__('Bạn đã cập nhật đoàn phí thành công!!', true),'success');    
        }
        //lay thong tin chi doan hien dang cap nhat doan phi
        $chidoansv=$this->Chidoansv->find('first',array(
                'conditions'=>array('Chidoansv.id'=>$chidoan),
                'fields'=>array('Chidoansv.id','tenchidoan','Khoa.tenkhoa','khoahoc_id'),
                'recursive'=>0));
        /** ***********************************
         * Lay data cot dong
         *************************************** */
        //tim cac sinhvien thuoc chidoan (lay id cua sv)
        $cb=$this->Tgiachidoancb->find('list',array(
                'conditions'=>array('chidoansv_id'=>$chidoan,'ngaychuyen'=>null),
                'fields'=>array('canbo_id','canbo_id'),'recursive'=>-1));
                
		//hien thi sinhvien can cu vao $sv 
        $canbos=$this->Canbo->find('list',array('conditions'=>array('Canbo.id'=>$cb),
                                                //'OR'=>array(array('ngayvaodoan !='=>NULL),
                                                //array('ngayvaodoan !='=>'0000-00-00')),'ngayttdoan '=>NULL),
                						'fields'=>array('id','name'),'recursive'=>-1));
        //lay cac thang cua namhoc
        $thangs=$this->Thang->find('list',array('fields'=>array('id','thang'),
                'conditions'=>array('namhoc_id'=>$namhoc)));
        
        /** ************************************
         * cac sinh vien da dong doan phi
         *************************************** */
        if(!empty($thangs))
        	$thang_id = array_keys($thangs);
        else
        	$thang_id= array();
        $doanphi = $this->Doanphicb->find('list',array(
        					'fields'=>array('thang_id','sotien'),
        					'conditions'=>array('thang_id'=>$thang_id)));
        //cot "name" dc dinh  nghia trong Model cua Tgdongdoanphicb, lay du lieu de kiem tra xem sinhvien do
        //da dong vao thang nao
        $dulieu=$this->Tgdongdoanphicb->find('list',array(
                'conditions'=>array('namhoc_id'=>$namhoc,'chidoansv_id'=>$chidoan),
                'fields'=>array('name','ngaydong'),
                'recursive'=>-1));
	$urs=$this->Tgdongdoanphicb->find('all',array(
                'conditions'=>array('Tgdongdoanphicb.namhoc_id'=>$namhoc,'Tgdongdoanphicb.chidoansv_id'=>$chidoan),
                'fields'=>array('Tgdongdoanphicb.name',"CONCAT(Z1user.maso, '_', Z1user.hoten) as `Z1user__name`"),
                'recursive'=>1));
        $users = array();
        foreach ($urs as $ur)
        {
        	$users[$ur['Tgdongdoanphicb']['name']] =$ur['Z1user']['name'];
        }
        $dadongs = $this->Tgdongdoanphicb->find('all',array(
        					'fields'=>array('Tgdongdoanphicb.thang_id','count(Tgdongdoanphicb.canbo_id) as dadong'),
        					'conditions'=>array('Tgdongdoanphicb.namhoc_id'=>$namhoc,
        										'Tgdongdoanphicb.chidoansv_id'=>$chidoan,
        									 'OR'=>array(array('Canbo.ngayttdoan'=>null),
        												array('Canbo.ngayttdoan'=>'0000-00-00'))),
        					'group'=>array('thang_id')));
        
        $dathu = array();
        foreach ($dadongs as $dadong)
        {
        	$dathu[$dadong['Tgdongdoanphicb']['thang_id']] =$dadong[0]['dadong'] * $doanphi[$dadong['Tgdongdoanphicb']['thang_id']]; 
        }
        $nhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc),
        									'fields'=>array('id','namhoc')));

        $this->set(compact('canbos','thangs','chidoansv','namhoc','dulieu','nhoc','dathu','chidoan','doanphi','users'));
        
	}
	
	function xoaddong($ttsv=null, $ttcd = null)
	{
		if(!$ttsv || !$ttcd)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		$arrsv =split('_', $ttsv);
		$arrcd =split('_', $ttcd);
		$thg_id=$arrsv[0];
		$sv_id = $arrsv[1];
		$this->Tgdongdoanphicb->query('delete from tgdongdoanphicbs where canbo_id='.$sv_id .' and thang_id='.$thg_id);
		$cd = $arrcd[0];
		$nh = $arrcd[1];
		$this->redirect(array('action'=>'doanp',$cd,$nh));
	}
}
